/*==============================================================================
FILE NAME: Maps_processing.do
PURPORSE: Use previously created processed data to create csv files with data for map creation for TitleV RNs
CREATED: 25 August 2025
==============================================================================*/


// Step 1: Load investigations dataset
use "$processed_data/Air_Panel.dta", clear

duplicates drop RN_id, force

// Step 2: Merge with RN zipcodes data
merge 1:1 RN_id using "$processed_data/RN_zipcodes.dta"

// Step 3: Keep only matched observations (i.e., those in investigations)
keep if _merge == 3
drop _merge

// Step 4: Create count_RN only for TitleV == 1
gen count_RN = (TitleV == 1)

// Step 5: Collapse by ZIP code, summing count_RN
collapse (sum) count_RN, by(ZipCode)

// Step 6: Save the resulting dataset
save "$processed_data/zipcode_investigation_counts_TitleV.dta", replace

// Step 7: merge
merge 1:1 ZipCode using "$processed_data/data_for_maps.dta"
keep if _merge == 3 | _merge ==2
drop _merge
export delimited "$processed_data/data_for_maps_TitleV.csv", replace
